﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace ImportData
{
    public class ImportAccess : ImportMapDocument
    {

        public override MapData.MapDocument Load(string fileName) {
            return new MapData.MapDocument();
        }

        public override MapData.MapDocument Append(string fileName, MapData.MapDocument doc){

            if (!File.Exists(fileName))
            {
                throw new ImportException("File Does Not Exist");
            }
            /// Test to make sure the file is a Microsoft Access database file (.mdb).
            else if (!fileName.ToLower().EndsWith(".mdb")){
                throw new ImportException("File is not a Microsoft Access database file (.mdb)");
            }
            
            OleDbConnection db = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ElectoralData.mdb");
            db.Open();
            OleDbCommand myCommand = new OleDbCommand();
            myCommand.Connection = db;
            OleDbDataReader reader;
           
            double safety = 0;
            double currentMargin = 0;
            double PreviouslyWonFactor = 0;
            int winlose = 0;
            double StateImpactFactor = 0;
            
            string party;
            MapData.MapDocument returnDocument = new MapData.MapDocument();
            
            foreach (MapData.MapObject item in doc.getMapObjects()) {
                myCommand.CommandText = "SELECT * FROM 2004ElectionResultsQuery WHERE Seat='"+item.Name+"'";
                reader = myCommand.ExecuteReader();
                
                if (reader.HasRows) {
                    reader.Read();

                    party = (string)reader["Party"];

                    // Current Margin
                    currentMargin = (double)reader["Percentage"];

                    // Previously Won Factor
                    //double LNP = (double)reader["Liberal/National Coalition Percentage"];
                   // double ALP = (double)reader["Australian Labor Party Percentage"];
                    PreviouslyWonFactor = 1;
                    if (((int)reader["HeldFor"]) > 4) {
                        PreviouslyWonFactor += 0.1;
                    } else if (((int)reader["HeldFor"]) >= 2) {
                        PreviouslyWonFactor += 0.05;
                    }

                    // State Impact Factor
                    // swing = (double)reader["Swing"];
                    reader.Close();
                    myCommand.CommandText = "SELECT COUNT(1) AS cnt FROM PartyStateWins WHERE (Seat='" + item.Name + "') AND (Party_win = '" + party + "')";
                    winlose = (int) myCommand.ExecuteScalar();
                    StateImpactFactor = 1;
                    StateImpactFactor += (winlose) * 0.02;
               

                    myCommand.CommandText = "SELECT COUNT(1) AS cnt FROM PartyStateWins WHERE (Seat='" + item.Name + "') AND (Party_win <> '" + party + "')";
                    winlose = (int)myCommand.ExecuteScalar();
                    StateImpactFactor -= (winlose) * 0.04;
                    
                    safety = (currentMargin * PreviouslyWonFactor * StateImpactFactor);
                    //item.Description += "Safety = " + safety;
                    item.Description += "<B>Safety</b> - " + safety.ToString("F") +"<br>"+ getPhaseOneData(db, item.Name);
                    
                    if (item.GetType().Name.Equals("Polygon")) {
                        MapData.Polygon tmpItem = (MapData.Polygon)item;
                        if (safety <= 5) {
                            tmpItem.FillColor = "990000FF";
                        } else if (safety <= 10) {
                            tmpItem.FillColor = "993399FF";
                        } else if (safety <= 15) {
                            tmpItem.FillColor = "9900FFFF";
                        } else if (safety <= 20) {
                            tmpItem.FillColor = "99FFCC00";
                        } else {
                            tmpItem.FillColor = "9900FF00";
                        }
                        //System.Console.WriteLine(item.Name + " safety = " + safety);
                        returnDocument.addMapObject(tmpItem);
                    } else {
                        returnDocument.addMapObject(item);
                    }
                   
                    //System.Console.WriteLine("CURRENT PERCENT = " + currentMargin);

                } else { 
                    reader.Close();
                    db.Close();
                    returnDocument.addMapObject(item);
                }                
            }
            db.Close();
            return returnDocument;           
        }

        public string getPhaseOneData(OleDbConnection db, string region) {
            OleDbCommand myCommand = new OleDbCommand();
            myCommand.Connection = db;
            OleDbDataReader reader;
            myCommand.CommandText = "SELECT [FP ALP], [FP LP], [FP NP], [FP DEM], [FP GRN], [FP OTH] FROM [2004 Election Phase1] WHERE Electorate = '" + region + "'";
            reader = myCommand.ExecuteReader();
            if (reader.HasRows) { 
                reader.Read();
                double highest = 0;
                int i;
                for(i = 0; i < 6;i++) {
                    if (((double)reader[i])>highest) {
                        highest = (double)reader[i];
                    }
                }
                string returnValue = "<br><table width=\"150\" border=\"0\" cellspacing=\"2\" cellpadding=\"0\" style=\"font-size:9px; font-family:Arial, Helvetica, sans-serif; text-align:center; font-weight:bold\">";
                string[] colors = { "#FF0000", "#0000FF", "#FFFF00", "#00FFFF", "#00FF00", "#999999" };
                string[] parties = { "ALP", "LIB", "NP", "DEM", "GRN", "OTH" };
                for (i = 0; i < 6; i++) {
                    returnValue += "<tr><td width=\"30\">"+parties[i]+"</td>";
                    returnValue += "<td><table width=\""+Math.Round((((double)reader[i])/highest)*100)+"%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\"><tr><td bgcolor=\""+colors[i]+"\">&nbsp;</td></tr>";
                    returnValue += "</table></td><td width=\"20\">" + reader[i] + "%</td></tr>";
                }
                returnValue += "</table><br>"; 
                reader.Close();
                return returnValue;
            }
            return "";
        } 
    }
}